<?php

/** Class to handle sequential keys for any table.
 *
 *  This collection of functions (libary) is licensed under GPL2.
 *  See LICENSE or www.gnu.org for more details.
 *  @author Jyry Kuukkanen
 *  @copyright Jyry Kuukkanen
 *
 *  $Id: sodbseqkey.php,v 1.4 2005/11/09 19:14:13 jyry Exp $
 */


include_once("sodebug.php");
include_once("sosqlhi.php");
include_once("sodblock.php");
include_once("sogeneric.php");

/* seq key table related constants */
define(SOSEQ_KEYTABLE, "soseqkeys");
define(SOSEQ_TABLECOL, "tablename");
define(SOSEQ_SEQNAME, "seqname");
define(SOSEQ_LASTKEYCOL, "lastkey");

/** Sequential key handler class.
 *  A class to handle sequential keys. This is database server independent.
 *  Example
 *  $seq = new soDbSeqKey(soDbConnect("pgsql://user@host/db"),
 *                        array("trimming" => 1), "owner_id", "session_id");
 *  $serie = new soSet();
 *  @serie->additem("serie:int", 99);
 *  $seq->setTarget("tablename", "keycolname", $serie);
 *  if ($seq->startUpdate()) {
 *      echo "Next key to use ".$seq->getKey()."\n";
 *      echo "Commit returns ".$seq->finishUpdate("commit")."\n";
 *  } else {
 *      echo "Update did not start at all!\n";
 *  };
 *
 *  @var string $__Table Current table to get/set key value for
 *  @var string $__KeyCol Current table key column name
 *  @var soSet $__Serie Column names and values in serie. For example, if
 *      order table has a key structure of "serie char(1), id integer", where
 *      serie may be one of S=sales, C=contrats, A=amendments, then passing
 *      $set->setItem("serie", "S") as a serie, would guarantee, that orders
 *      with serie S runs with different sequence than, say, serie A.
 *  @var string $__SeqName Sequence name, plus sign delimited list of all
 *      serie column names and key column name, like "serie+id"
 *  @var string $__InitKey Initial value to use with table when no key
 *      available yet
 *  @var int $__RetryCount How many times to try locking
 *  @var int $__RetryPause How many secs to wait between locking attempts
 *  @var string $__Key Current key value
 *  @var soDa $__Soda Db access object
 *  @var soDbRowLock $__Lock Seq key table rows needs locking
 *  @var string $__Owner Owner to lock rows
 *  @var string $__SessionID Session ID for locking rows
 *  @var bool $__Transact Flag to indicate when key update is in progress
 *  @var object $__DbObj Database object, initialised in constructor, used by
 *      createTable routine
 *  @var array/any $__Options Database options, initialised in constructor,
 *      used by createTable routine
 */
class soDbSeqKey {
    var $__Table;
    var $__SeqName;
    var $__InitKey = 0;
    var $__RetryCount;
    var $__RetryPause;
    var $__Key; 
    var $__Soda;
    var $__Lock;
    var $__Owner;
    var $__SessionID;
    var $__Transact = 0;
    var $__DbObj;
    var $__Options;


    /** constructor
     *  Class to handle sequential counters for different tables.
     *  @param object $DbObj Db object (PEAR)
     *  @param array/any $Options Db options.
     *  @param string $SessionID Session ID string for seq key row locking.
     *  @param int $RetryCount How many times to try locking seq key row.
     *  @param int $RetryPause How many seconds to wait between locking retries.
     *  @package soDbSeqKey
     */
    function soDbSeqKey(&$DbObj, $Options = array(), $Owner = "", 
                        $SessionID = "", $RetryCount = 5, $RetryPause = 2) {

        $this->__Soda = new soDa($DbObj, $Options);
        $this->__Lock = new soDbRowLock($DbObj, $Options);
        
        $this->__Soda->setTable(SOSEQ_KEYTABLE);

        $this->setOwner($Owner, $SessionID);
        $this->__RetryCount = $RetryCount;
        $this->__RetryPause = $RetryPause;
        $this->__DbObj = $DbObj;
        $this->__Options = &$Options;

        soDebug("soDbSeqKey->constructor: exit ok", SOD_DET);
    } // constructor


    /** Sets owner & session id
     *  @param string $Owner @Owner
     *  @param string $SessionID Session id
     *  @package soDbSeqKey
     */
    function setOwner($Owner, $SessionID) {
        $this->__Owner = $Owner;
        $this->__SessionID = $SessionID;
    } // setOwner


    /** Creates seq key table.
     *  @return bool Returns SQL execution return value: true(1)=ok, false(0)=not so much.
     *  @package soDbSeqKey
     */
    function __createTable() {
        $this->__Soda->setAction(SODA_CREATE);
        $this->__Soda->setSection(SODA_DATA,
                                  array(
                                  SOSEQ_TABLECOL,
                                  SOSEQ_SEQNAME,
                                  SOSEQ_LASTKEYCOL,
                                  "*"),
                                  array(
                                  "char(30) not null",
                                  "char(40) not null",
                                  "integer",
                                  SOSEQ_TABLECOL.",".SOSEQ_SEQNAME));
        $result = $this->__Soda->run();

        return $result;
    } // __createTable


    /** Inserts initial key value for a table.
     *  @return bool soDa result, true(1) when insert statement ran ok.
     */
    function __insertInitKey() {
        soDebug("soDbSeqKey->__insertInitKey ".$this->__InitKey." for ".
                $this->__Table, SOD_DET);

        $this->__Soda->setAction(SODA_INSERT);
        $this->__Soda->setSection(SODA_DATA,
                                  array(
                                  SOSEQ_LASTKEYCOL.":int",
                                  SOSEQ_SEQNAME,
                                  SOSEQ_TABLECOL),
                                  array(
                                  $this->__InitKey,
                                  $this->__SeqName,
                                  $this->__Table));

        return $this->__Soda->run();
    } // __insertInitKey


    /** Gets the current last key.
     *  @param bool $AutoSkip When set, checks lastkey from the target table
     *      and skips until first non-existing value is found. The purpose of
     *      this is simply ensure, that when seqential key is out of sync with
     *      the target table.
     *  @param bool $AutoCreate When set, tries creating the seq key table if
     *      when encountered SQL error.
     *  @return int Returns the current last key or -1 when the table
     *      is not set or no key is found for the current table.
     *  @package soDbSeqKey
     */
    function __queryKey($AutoSkip = 1, $AutoCreate = 1) {
        if ($this->__Table == "") {
            soDebug("soDbSeqKey->__queryKey: null table name", SOD_HI);
            return -1; // <-- EXIT
        };

        $this->__Soda->setAction(SODA_SELECT);
        $this->__Soda->setSection(SODA_DATA, SOSEQ_LASTKEYCOL.":int");
        $this->__Soda->setSection(SODA_WHERE,
                                  array(
                                  SOSEQ_TABLECOL,
                                  SOSEQ_SEQNAME),
                                  array(
                                  $this->__Table,
                                  $this->__SeqName));
        if ($this->__Soda->run()) {
            $this->__Soda_res = &$this->__Soda->Results;
            if ($this->__Soda_res->count()) {
                $result = intval($this->__Soda_res->getValue(SOSEQ_KEYTABLE.".".
                                                     SOSEQ_LASTKEYCOL));
            } else {
                if ($this->__insertInitKey()) $result = $this->__InitKey;
            }; // else if
        } else {
            /* SQL execution failed. Maybe creating the table could help? If
             * it does, then try querying again by calling this func without
             * AutoCreate feature (this if-block) */
            if (($AutoCreate) && ($this->__createTable())) {
                $result = $this->__QueryKey(0);
            } else {
                $result = NULL;
            };
        };

        if (($result != -1) && $AutoSkip) $result = $this->__checkKey($result);

        soDebug("soDbSeqKey->__queryKey: table ".$this->__Table.
                " resulted '$result'", SOD_HI);
        return $result;
    } // __queryKey


    /** Checks and skips key until it is not found in the target table.
     *  @param int $Key Key to check & skip
     *  @return int Returns next available key or -1 on error
     */
    function __checkKey($Key) {
        if ($this->__SeqName == "") {
            soDebug("soDbSeqKey->__checkKey: no seq.name for table ".
                     $this->__Table, SOD_LO);
            return -1; // <-- EXIT
        };

        $soda = new soDa($this->__DbObj, $this->__Options);

        $count_col = $this->__Table.".count(*):int";
        $soda->setTable($this->__Table, 1);
        $soda->setSelectTablePrefix(0);
        $soda->setAction(SODA_SELECT);
        $soda->setSection(SODA_DATA, $count_col, array());

        /* Prepare columns and values in seqname for soDa query */
        $serie = soStrToAArray(soExplode($this->__SeqName, "++"), 1, "==");
        list($cols, $vals) = soSplitAArray($serie);
        $c = count($cols) -1;
        if (soStrPos($cols[$c], ":") == -1) $cols[$c] .= ":int";

        /* Loop until non-existing value found or sql query error occurs */
        $result = $Key;
        $done = 0;
        while (!$done && ($result != NULL)) {
            $vals[$c] = $Key;
            $soda->setSection(SODA_WHERE, $cols, $vals);
            if ($soda->run()) {
                if ($soda->getDataVal($count_col)) {
                    $Key++;
                } else {
                    /* No rows found for this key -> found what we wanted
                     * -> return the previous no. as it is the last used key */
                    $result = $Key -1;
                    $done = 1;
                };
            } else {
                $result = NULL;
            };
        }; // while

        return $result;
    } // __checkKey


    /** Locks the row for update.
     *  @param SOLM_* $Method Locking method, see soLock for details.
     *  @return SOLS_* Lock status.
     */
    function __lockRow($Method) {
        soDebug("soDbSeqKey->__lockRow: enter with table='".$this->__Table."', sesid='".
                $this->__SessionID."', method=$Method", SOD_DET);
        $this->__Lock->setParams(SOSEQ_KEYTABLE,
                                 array($this->__Table,
                                 $this->__SeqName),
                                 $this->__Owner,
                                 $this->__SessionID);
        return $this->__Lock->execute($Method); // <-- EXIT
    } // __lockRow


    /** Starts an key update.
     *  Tries locking seq key row for current table and gets next value.
     *  @return bool Returns true(1) on success or false(0) on error, such as no
     *      table set or locking failed.
     */
    function startUpdate() {
        if (($this->__Transact) ||
            ($this->__Table == "")) {
            soDebug("soDbSeqKey->startUpdate: transaction active (".
                    $this->__Transact.") or table ('".
                    $this->__Table."') not set error -- exiting!", SOD_HI);
            return 0; // <-- EXIT
        };

        soDebug("soDbSeqKey->startUpdate: enter", SOD_DET);

        /* Try to get a lock for the key */
        $i = $this->__RetryCount;
        do {
            $locked = ($this->__lockRow(SOLM_NORMAL) == SOLS_OWN);
            $i--;
        } while ((!$locked) && ($i > 0));

        if (!$locked) return 0; // <-- EXIT

        /* Locked ok - next get the last key */
        $this->__Key = $this->__queryKey();

        /* Something wrong? */
        if (soIsNull($this->__Key)) {
            soDebug("soDbSeqKey->startUpdate: query failed", SOD_LO);
            $this->__unlock();
            return 0; // <-- EXIT
        };

        /* Advance the last key to get to the next available key */
        $this->__Key++;

        $this->__Transact = 1;
        return 1;
    } // startUpdate


    /* Commits next key back to to seq key table or cancels the update.
     * Unlocks the row and if commiting, then writes the next key back to table.
     * @param string $Method Either "cancel" or "commit". Anything but
     *     "commit" == cancel.
     * @return bool True(1) on success or false(0) on error such as no
     *     transaction has been started or due to SQL executing problem.
     */
    function finishUpdate($Method = "cancel") {
        if (!$this->__Transact) return 0; // <-- EXIT

        soDebug("soDbSeqKey->finishUpdate(".$Method."): enter", SOD_DET);

        if ($Method == "commit") {
            $this->__Soda->setAction(SODA_UPDATE);
            $this->__Soda->setSection(SODA_DATA, SOSEQ_LASTKEYCOL.":int",
                                      array($this->__Key));
            $this->__Soda->setSection(SODA_WHERE, 
                                      array(SOSEQ_TABLECOL, SOSEQ_SEQNAME),
                                      array($this->__Table, $this->__SeqName));
            if (!$this->__Soda->run()) return 0; // <-- EXIT
        }; // if commit

        $this->__Transact = 0;
        return $this->__unlock();
    } // finishUpdate


    /** Unlocks seq key row
     *  @return
     */
    function __unlock() {
        $status = $this->__lockRow(SOLM_UNLOCK);
        $result = ($status == SOLS_NONE);
        if (!$result) {
            soDebug("soDbSeqKey: unlock failed ($status)", SOD_DET);
        }
        return $result;
    } // __unlock

    /** Sets the target to query/update next key value for.
     *  Target consists of table name and optionally key column name and
     *  serie.
     *  @param string $TableNames Table name
     *  @param string $KeyCol Key column name in the table. This is optional,
     *      but should be used when auto skip feature is used.
     *  @param array||soSet||string $Seq Optional sequense columns (as keys)
     *       and values when table has more than one key column.
     *  @param string $InitKey Initial key value to use and set, when no key
     *      value exists.
     *  @return bool Returns true(1) on success or false(0) on error
     *      (transaction in progress).
     *  @package soDbSeqKey
     */
    function setTarget($TableName, $Seq, $InitKey = 1) {
        if ($this->__Transact) {
            soDebug("soDbSeqKey->setTable: error -- transaction in progress",
                    SOD_HI);
            return 0; // <-- EXIT
        } else {
            if (($TableName == "") || (!soIsNum($InitKey))) {
                return 0; // <-- EXIT
            } else {
                if (is_array($Seq)) {
                    $set = new soSet();
                    $set->joinArray($Seq);
                    $Seq = $set;
                };

                $serie = "";
                if (soGetClass($Seq) == "soset") {
                    $serie = $Seq->toString(1, "++", "==");
                };
                if (!$serie) $serie = $Seq;

                $this->__Table = $TableName;
                $this->__KeyCol = soExtractVal($KeyCol, ":", 1);
                $this->__SeqName = $serie;
                $this->__InitKey = $InitKey;
            }; // if
        }; // if transact

        return 1;
    } // setTarget


    /** Get next key for current table.
     *  Returns the next key value for current table.
     *  @return string Next key value or empty string "" on error.
     *  @package soDbSeqKey
     */
    function getKey() {
        return $this->__Key;
    } // getKey


} // class soDbSeqKey


/*
# test code

include_once("sodebug.inc");
$soDebugLevel = SOD_DET;

$dbo = soDbConnect("pgsql://postgres@hiiri/sothis");
$opts = array("trimming" => 1, "incase" => 1,
              "transact" => 1, "date" => "j.40");

$serie = array("toimipiste" => "VUO", "tpnro" => "");
//$serie = new soSet();
//$serie->addItem("firma", "LLE");
//$serie = "nro";
$seq = new soDbSeqKey($dbo, $opts);
$seq->setTarget("las10", $serie);
$seq->startUpdate();
echo "key=".$seq->getKey()."\n";
$seq->finishUpdate("commit");

*/

?>
